package pe.gob.osinergmin.interfase_siaf.dac.dao.impl;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import pe.gob.osinergmin.interfase_siaf.dac.dao.ExpedienteIngresoDao;
import pe.gob.osinergmin.interfase_siaf.dac.domain.ExpedienteIngreso;


@Repository("expedienteIngresoDao")
public class ExpedienteIngresoDaoImpl implements ExpedienteIngresoDao{

	// private final Log logger = LogFactory.getLog(getClass());
		
		
	 private NamedParameterJdbcTemplate jdbcParameterTemplateData;	
	// private JdbcTemplate jdbcTemplateData;	
	 
	 @Autowired
	 public void SetDataSource(DataSource dataSourceData){
		//this.jdbcTemplateData = new JdbcTemplate(dataSourceData);
		this.jdbcParameterTemplateData = new NamedParameterJdbcTemplate(dataSourceData);

	 }

	 
	 /**DECLARACION DE SQL**/
	 private static final String SQL_SELECT_COUNT_EXPINGRESO_EXP =
				" SELECT COUNT(expediente) FROM expediente_ingreso WHERE expediente=:expediente ";
	 
	 private static final String SQL_SELECT_COUNT_EXPINGRESO_FASE =
				" SELECT COUNT(fase) FROM expediente_ingreso WHERE fase=:fase ";
	 
	 private static final String SQL_SELECT_COUNT_EXPINGRESO_CORRE =
				" SELECT COUNT(correlativ) FROM expediente_ingreso WHERE correlativ=:correlativ ";
	 
	 private static final String SQL_SELECT_COUNT_EXPINGRESO_SEC =
				" SELECT COUNT(secuencia) FROM expediente_ingreso WHERE secuencia=:secuencia ";
		
	 
	 private static final String SQL_SELECT_EXPINGRESO_MONTO =
				" SELECT SUM(monto) AS monto FROM expediente_ingreso WHERE expediente=:expediente AND "
				+ " secuencia=:secuencia AND fase=:fase AND correlativ=:correlativ AND ano_eje =:anoEje AND ciclo =:ciclo ";
	 
	 private static final String SQL_SELECT_EXPINGRESO_MONTO_MN =
				" SELECT SUM(monto_naci) AS monto_naci FROM expediente_ingreso WHERE expediente=:expediente AND "
				+ " secuencia=:secuencia AND fase=:fase AND correlativ=:correlativ AND ano_eje =:anoEje AND ciclo =:ciclo ";
	 
	 
	 private static final String SQL_COUNT_EXP_INGRESO =
				" SELECT COUNT(expediente) FROM expediente_ingreso WHERE expediente=:expediente AND "
				+ " secuencia=:secuencia AND fase=:fase AND correlativ=:correlativ AND ano_eje =:anoEje AND ciclo =:ciclo ";
	 
    /**IMPLEMENTACION DE METODOS**/
	 
	 //Metodos para validacion de I-D -Amplicacion
	 @Override
	 public int verificarExpedienteDA(ExpedienteIngreso exp) throws Exception{
		 SqlParameterSource parameters = new BeanPropertySqlParameterSource(exp);
		 return this.jdbcParameterTemplateData.queryForInt(SQL_SELECT_COUNT_EXPINGRESO_EXP, parameters);
	 }
	 
	 @Override
	 public int verificarFaseDA(ExpedienteIngreso exp) throws Exception{
		 SqlParameterSource parameters = new BeanPropertySqlParameterSource(exp);
		 return this.jdbcParameterTemplateData.queryForInt(SQL_SELECT_COUNT_EXPINGRESO_FASE, parameters);
	 }
	 
	 @Override
	 public int verificarSecuenciaDA(ExpedienteIngreso exp) throws Exception{
		 SqlParameterSource parameters = new BeanPropertySqlParameterSource(exp);
		 return this.jdbcParameterTemplateData.queryForInt(SQL_SELECT_COUNT_EXPINGRESO_SEC, parameters);
	 }
	 
	 @Override
	 public int verificarCorrelativoDA(ExpedienteIngreso exp) throws Exception{
		 SqlParameterSource parameters = new BeanPropertySqlParameterSource(exp);
		 return this.jdbcParameterTemplateData.queryForInt(SQL_SELECT_COUNT_EXPINGRESO_CORRE, parameters);
	 }
	 
	 
	 @Override
     public List<ExpedienteIngreso> bucarMontoTotalDeterminado(ExpedienteIngreso exp) throws Exception {		 
			SqlParameterSource parameters = new BeanPropertySqlParameterSource(exp);
			RowMapper<ExpedienteIngreso> lista = ParameterizedBeanPropertyRowMapper.newInstance(ExpedienteIngreso.class);		
		    return this.jdbcParameterTemplateData.query(SQL_SELECT_EXPINGRESO_MONTO, parameters, lista);	
    }
	 
	@Override
    public List<ExpedienteIngreso> bucarMontoMnTotalDeterminado(ExpedienteIngreso exp) throws Exception {		 
			SqlParameterSource parameters = new BeanPropertySqlParameterSource(exp);
			RowMapper<ExpedienteIngreso> lista = ParameterizedBeanPropertyRowMapper.newInstance(ExpedienteIngreso.class);		
		    return this.jdbcParameterTemplateData.query(SQL_SELECT_EXPINGRESO_MONTO_MN, parameters, lista);	
    } 
	
	
	 @Override
	 public int verificarExiteExpIngreso(ExpedienteIngreso exp) throws Exception{
		 SqlParameterSource parameters = new BeanPropertySqlParameterSource(exp);
		 return this.jdbcParameterTemplateData.queryForInt(SQL_COUNT_EXP_INGRESO, parameters);
	 }
	 
	 
}
